package org.proxycine.gestsalles.model.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.proxycine.gestsalles.model.entities.Cinema;
import org.proxycine.gestsalles.model.entities.Copie;
import org.proxycine.gestsalles.model.entities.EtatCopie;

public final class CopieDao extends Adapter {
	
	private static CopieDao instance;
	
	private CopieDao() {
		instance = this;
	}
	
	public static CopieDao getInstance() {
		if(instance == null)
			instance = new CopieDao();
		
		return instance;
	}
	
	public Copie getCopieFromId(int id) 
	{
		Copie copie = null;
		String request = "SELECT * " +
				"FROM Copie Co " +
				"WHERE Co.id = '" + id + "'";
				
		try {
			ResultSet rs = getStatement().executeQuery(request);
			
			if (rs.next()) {
				copie = new Copie(rs.getInt("id"),
						FilmDao.getInstance().getFilm(rs.getInt("film")),
						CinemaDao.getInstance().getCinema(rs.getInt("cinema")),
						EtatCopie.valueOf(rs.getString("etat")));
			}
			
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}			
				
		return copie;	
		
	}
	
	public int getNbCopiesDisponibles(int idFilm) {
		String request = "SELECT COUNT(*) AS `nb` FROM Copie WHERE film = "+idFilm + " AND etat = 'Achete' ";
		int res = 0;
		try {
			ResultSet rs = getStatement().executeQuery(request);			
			while (rs.next())
				res = rs.getInt("nb") ;
			
			rs.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}		
		return res;
	}
	
	public List<Copie> getCopiesDisponibles() {
		
		List<Copie> copiesDispo = new ArrayList<Copie>();
		String request = "SELECT * FROM Copie WHERE etat = 'Achete' ";
		try {
			ResultSet rs = getStatement().executeQuery(request);			
			while (rs.next()) {
				copiesDispo.add(new Copie(rs.getInt("id"),
						FilmDao.getInstance().getFilm(rs.getInt("film")),
						CinemaDao.getInstance().getCinema(rs.getInt("cinema")),
						EtatCopie.valueOf(rs.getString("etat"))));
			}
			rs.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		
		return copiesDispo;
	}
	
	public List<Copie> getAllCopies(){
		List<Copie> copies = new ArrayList<Copie>();
		String request = "SELECT * FROM Copie";
		try {
			ResultSet rs = getStatement().executeQuery(request);			
			while (rs.next()) {
				copies.add(new Copie(rs.getInt("id"),
						FilmDao.getInstance().getFilm(rs.getInt("film")),
						CinemaDao.getInstance().getCinema(rs.getInt("cinema")),
						EtatCopie.valueOf(rs.getString("etat"))));
			}
			rs.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		
		return copies;
	}

	public Copie getCopieFromFilmName(String film_name, String login_gerant)
	{
		Copie copie = null;
		String request = "SELECT Co.* " +
				"FROM Copie Co, Film F, Employe E, Cinema Ci " +
				"WHERE E.login = Ci.gerant " +
				"AND Ci.id = Co.cinema " +
				"AND Co.film = F.id " +
				"AND E.login = '" + login_gerant + "' " +
				"AND F.titre = '" + film_name + "'";
		try {
			ResultSet rs = getStatement().executeQuery(request);
			
			if (rs.next()) {
				copie = new Copie(rs.getInt("id"),
						FilmDao.getInstance().getFilm(rs.getInt("film")),
						CinemaDao.getInstance().getCinema(rs.getInt("cinema")),
						EtatCopie.valueOf(rs.getString("etat")));
			}
			
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return copie;
	}
	
	public int affecterCopieAUnCinema(Copie copie, int idCinema) {		
        String request = "UPDATE Copie SET cinema = '"+idCinema+"', etat = 'Distribue' " +
        		 		 "WHERE id = "+ copie.getId();
                
        try {
        	    getStatement().executeUpdate(request);		
                
        } catch (SQLException e) {
                e.printStackTrace();
        }		
		
		return 0;
	}
	
	public void setEtatCopieAchete(Copie copie){
		String request = "UPDATE Copie SET etat = '" + EtatCopie.Achete + "', " +
		 		 "cinema = NULL WHERE id = "+ copie.getId();
		 try {
     	    getStatement().executeUpdate(request);		             
	     } catch (SQLException e) {
	             e.printStackTrace();
	     }		
		
	}
	
	public List<Copie> getCopiesDistribueesParCinema(Cinema cinema) {
		List<Copie> copiesDistribuees = new ArrayList<Copie>();
		
		String request = "select * from copie where cinema = " + cinema.getId() + " and etat = 'Distribue'";
		
		try {
			ResultSet rs = getStatement().executeQuery(request);			
			while (rs.next()) {
				copiesDistribuees.add(new Copie(rs.getInt("id"),
						FilmDao.getInstance().getFilm(rs.getInt("film")),
						CinemaDao.getInstance().getCinema(rs.getInt("cinema")),
						EtatCopie.valueOf(rs.getString("etat"))));
			}
			rs.close();
		} 
		catch (SQLException e) {
			e.printStackTrace();
		}
		
		return copiesDistribuees;
	}
	
	/**
	 * 
	 * @param copie
	 * @return id of inserted copie
	 */
	public int insertCopie(Copie copie, int idFilm) {
		ResultSet generatedKeys = null;
		int idCopie = -1;

		try {
			PreparedStatement stmt = getConnection()
					.prepareStatement(
							"INSERT INTO Copie (id, film, cinema, etat) "
									+ "VALUES (?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);


			stmt.setString(1, null);
			stmt.setInt(2, idFilm);
			
			if (copie.getCinema() != null)
				stmt.setInt(3, copie.getCinema().getId());
			else
				stmt.setString(3, null);
			
			stmt.setString(4, copie.getEtat().name());

			stmt.executeUpdate();

			generatedKeys = stmt.getGeneratedKeys();
			if (generatedKeys.next()) {
				idCopie = generatedKeys.getInt(1);
			} else {
				throw new SQLException(
						"Creating copie failed, no generated key obtained.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {}
		}

		return idCopie;
	}	
}
